import pandas as pd
import openpyxl


# 读取文件的第一个sheet返回某列数据，返回数组
def readByColumn(excel_file_path, column_num):
    # 使用pandas的read_excel函数读取前99行数据
    df = pd.read_excel(excel_file_path, nrows=4089)
    array = []
    # 遍历DataFrame中的每一行
    for index, row in df.iterrows():
        # 打印第一列和第四列的数据
        # print(f"\"{row[df.columns[0]]}\":\"{row[df.columns[3]]}\",")

        array.append(row[df.columns[column_num]])
        # print("hello")
        # print(f"{row[df.columns[8]]}")
    return array


# 读取文件的第 index 个 sheet，返回某列数据，返回数组
def readColumnFromSheet(excel_file_path, index, column):
    try:
        # 加载 Excel 文件
        workbook = openpyxl.load_workbook(excel_file_path, data_only=True)
        sheet_names = workbook.sheetnames

        # 检查索引是否有效
        if index < 0 or index >= len(sheet_names):
            raise IndexError("Sheet index out of range.")

        # 获取指定的工作表
        sheet = workbook[sheet_names[index]]

        # 检查列索引是否有效
        if column <= 0:
            raise ValueError("Column index must be positive.")

        # 提取列数据
        column_data = []
        for row in sheet.iter_rows(min_col=column, max_col=column, values_only=True):
            column_data.append(row[0])  # 取该列的数据

        return column_data
    except Exception as e:
        print(f"Error reading column {column} in sheet {index}: {e}")
        return []


# 读取文件，返回每个 sheet 的名称
def readSheets(excel_file_path):
    """
    读取 Excel 文件，返回所有 sheet 的名称
    :param excel_file_path: Excel 文件路径
    :return: List[str]，sheet 名称列表
    """
    try:
        workbook = openpyxl.load_workbook(excel_file_path, read_only=True)
        sheet_names = workbook.sheetnames
        return sheet_names
    except Exception as e:
        print(f"Error reading sheets: {e}")
        return []


# 读取文件，返回 index 个 sheet 的第 i 行，第 j 列数据
def readSheetByIndexIJ(excel_file_path, index, i, j):
    """
    读取 Excel 文件中指定 sheet 的第 i 行第 j 列的数据
    :param excel_file_path: Excel 文件路径
    :param index: sheet 索引（从 0 开始）
    :param i: 行号（从 1 开始）
    :param j: 列号（从 1 开始）
    :return: 单元格内容
    """
    try:
        workbook = openpyxl.load_workbook(excel_file_path, read_only=True)
        sheet_names = workbook.sheetnames

        # 验证 index 是否有效
        if index < 0 or index >= len(sheet_names):
            raise IndexError(f"Sheet index {index} is out of range.")

        sheet = workbook[sheet_names[index]]

        # 验证行列号是否有效
        if i <= 0 or j <= 0:
            raise ValueError("Row and column numbers must be positive integers starting from 1.")

        # 获取单元格内容
        cell_value = sheet.cell(row=i, column=j).value
        return cell_value
    except Exception as e:
        print(f"Error reading cell ({i}, {j}) in sheet index {index}: {e}")
        return None


if __name__ == '__main__':
    # readByColumn('F:\项目\校友汇\毕业student_detail.xlsx', 8)
    # 示例文件路径
    excel_file_path = "F:\项目\BI\月份数据\\2024年1月学校主体月度预算表_实际数_人民币_默认版本.xlsx"

    # 获取第2个 sheet 中第 8 列的数据
    column_data = readColumnFromSheet(excel_file_path, index=1, column=8)
    print("Column data:", column_data)
    # # 读取所有 sheet 名称
    # sheet_names = readSheets(excel_file_path)
    # print("Sheet Names:", sheet_names)
    #
    # cell_value = readSheetByIndexIJ(excel_file_path, index=0, i=4, j=3)
    # print("Cell Value:", cell_value)
    # cell_value = readSheetByIndexIJ(excel_file_path, index=1, i=4, j=3)
    # print("Cell Value:", cell_value)
